from django.shortcuts import HttpResponse
from django.db.models import Avg, Count, Max, Min, Sum, F, Q
from django.db import connection
from front.models import Book, Author, BookOrder


def index(request):
    # TODO: 聚合函数
    # 1.Avg：求平均值。比如想要获取所有图书的价格平均值
    result = Book.objects.aggregate(avg=Avg('price'))
    print(connection.queries)
    print(result)
    return HttpResponse('success')


def index2(request):
    # result = Book.objects.aggregate(avg=Avg('bookorder__price'))
    # print(result)
    # print(connection.queries)

    books = Book.objects.annotate(avg=Avg('bookorder__price'))
    for book in books:
        print('%s/%s' % (book.name, book.avg))
    print(books.query)
    return HttpResponse('index2')


def index3(request):
    # TODO: 聚合函数
    # 2.Count：获取指定的对象的个数
    # TODO: 获取图书总数量
    # total = Book.objects.aggregate(count=Count('id'))
    # print(total)
    # print(connection.queries)

    # TODO: 获取作者邮箱总数(去除重复邮箱)
    # total = Author.objects.aggregate(count=Count('email', distinct=True))
    # print(total)
    # print(connection.queries)

    # TODO: 获取每本书的销量
    books = Book.objects.annotate(count=Count('bookorder__id'), avgprice=Avg('bookorder__price'))
    for book in books:
        print('name:%s/avg price:%s/sold count:%s' % (book.name, book.avgprice, book.count))
    print(books.query)
    return HttpResponse('index3')


def index4(request):
    # TODO: 聚合函数
    # 3.Max和Min：获取指定对象的最大值和最小值。
    # 如：获取Author表中，最大的年龄和最小的年龄分别是多少
    # result = Author.objects.aggregate(max=Max('age'), min=Min('age'))
    # print(result)
    # print(connection.queries)

    # 获取每本图书销售的最高价、最低价
    books = Book.objects.annotate(max=Max('bookorder__price'), min=Min('bookorder__price'))
    for book in books:
        print('name: %s - max sold price: %s - min sold price: %s' % (book.name, book.max, book.min))
    print(books.query)
    return HttpResponse('index4')


def index5(request):
    # TODO: 聚合函数
    # 4.Sum：求指定对象的总和

    # 1.求所有图书的销售总额
    # total = BookOrder.objects.aggregate(total=Sum('price'))
    # print(total)
    # print(connection.queries)

    # 2.每一本图书的销售总额
    # books = Book.objects.annotate(total=Sum('bookorder__price'))
    # for book in books:
    #     print('name: %s - total: %s' % (book.name, book.total))
    # print(books.query)

    # 3.先求2020年度，销售总额
    # result = BookOrder.objects.filter(create_time__year=2020).aggregate(total=Sum('price'))
    # print(result)
    # print(connection.queries)

    # 4.求每一本图书在2020年度的销售总额
    books = Book.objects.filter(bookorder__create_time__year=2020).annotate(total=Sum('bookorder__price'))
    for book in books:
        print('name: %s - total: %s' % (book.name, book.total))
    print(books.query)

    return HttpResponse('index5')


def index6(request):
    # TODO: F表达式
    # 1.将每本图书的价格+10元
    # Book.objects.update(price=F('price')+10)
    # print(connection.queries[-1])

    # 2.查找作者表中name和email相同的数据
    authors = Author.objects.filter(name=F('email'))
    for author in authors:
        print(author)
    print(authors.query)
    return HttpResponse('index6')


def index7(request):
    # TODO: Q表达式
    # 1.获取价格大于100，并且评分在4.85分以上的图书
    # books = Book.objects.filter(Q(price__gte=100) & Q(rating__gte=4.85))
    # books = Book.objects.filter(price__gte=100, rating__gte=4.85)

    # 2.获取价格低于100元，或者评分低于4分的图书
    # books = Book.objects.filter(Q(price__lt=100) | Q(rating__lt=4))

    # 3.获取价格大于100，并且图书名字中不包含”传“字的图书
    books = Book.objects.filter(Q(price__gt=100) & ~Q(name__icontains='传'))

    for book in books:
        print(book)
    print(books.query)
    return HttpResponse('index7')